#include "TableView.h"
#include <QDesktopServices>
#include <QUrl>
#include <QDir>
#include <QFileDialog>
#include <QDebug>
#include "ChartView.h"

QList<QPointF> PointCould;
QList<QList<QVariant>> ret;//将Qvariant转换为QList<QList<QVariant>>
QList<QString> m_pItem;   //下拉框数据  表头

int TABLE_COL ; //表格列
int TABLE_ROW ; //表格行

CTableView::CTableView(QWidget *parent) : QWidget(parent)
{
    directory = QDir::currentPath();

    m_pVboxLay = new QVBoxLayout(this);
    m_pMybar1 = new QToolBar();
    m_pMybar2 = new QToolBar();

    m_pChoice = new QPushButton("加载样本数据");  //xuanzhewenjian
    m_pChoice->setStyleSheet("font-family:'幼圆';font-size:20px;"
                            "background-color: rgb(255, 255, 255);"
                            "color:#43CD80;"
                            "border-color:#43CD80;");

    m_pFileName =new  QLineEdit();
    m_pFileName->setFixedWidth(300);
    m_pAddDate = new QPushButton("加载数据");
    m_pAddDate->setStyleSheet("font-family:'幼圆';font-size:20px;"
                            "background-color: rgb(255, 255, 255);"
                            "color:#43CD80;"
                            "border-color:#43CD80;");
    m_pDaochuDate = new QPushButton("导出数据");
    m_pDaochuDate->setStyleSheet("font-family:'幼圆';font-size:20px;"
                            "background-color: rgb(255, 255, 255);"
                            "color:#43CD80;"
                            "border-color:#43CD80;");
    m_pMybar1->addWidget(m_pChoice);

    m_pSearch = new QLabel("Search:");
    m_pSearch->setStyleSheet("font-family:'幼圆';font-size:20px;");
    m_pSearch->setFixedWidth(92);
    m_pSearchLine = new QLineEdit();
    m_pSearchLine->setFixedWidth(300);


    m_pDateTable = new QTableWidget();

    QTableWidgetItem        *headerItem;
    QStringList             headerText;
    QHeaderView *header = m_pDateTable->verticalHeader();
    header->setHidden(true);

    m_pDateTable->setRowCount(0);

//    m_pDateTable->setColumnCount(headerText.count());
    /*整行被选中*/
    m_pDateTable->setSelectionBehavior(QAbstractItemView::SelectRows);
    //自动使用宽度
    m_pDateTable->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    //自动适应高度
//    m_pDateTable->verticalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    m_pDateTable->setFrameShape(QFrame::NoFrame);//设置无边框
    m_pDateTable->setShowGrid(false); //设置不显示格子线

//    m_pDateTable->setStyleSheet("background:rgb(56,56,56);alternate-background-color:rgb(48,51,55);selection-background-color:qlineargradient(x1:0,y1:0, x2:0, y2:1, stop:0 rgb(56,56,56),stop:1 rgb(76,76,76));"); //设置选中背景色
//    m_pDateTable->horizontalHeader()->setStyleSheet("QHeaderView::section{background-color:qlineargradient(x1:0, y1:0, x2:0, y2:1, stop:0 rgb(46,46,46),stop:1rgb(66,66,66));color: rgb(210,210,210);;padding-left: 4px;border: 1px solid#383838;}"); //设置表头背景色


    m_pDateTable->setFocusPolicy(Qt::NoFocus);//去除选中虚线框

    m_pDateTable->setAlternatingRowColors(true);
    m_pDateTable->setEditTriggers(QAbstractItemView::NoEditTriggers);

    for(int i =0; i <m_pDateTable->columnCount();i++ )
    {
        headerItem=new QTableWidgetItem(headerText.at(i));
        QFont font = headerItem->font();
        font.setBold(true);
        font.setPointSize(12);
        m_pDateTable->setHorizontalHeaderItem(i,headerItem);

//        headerItem->setFlags(headerItem->flags() &~Qt::ItemIsEnabled & ~Qt::ItemIsSelectable);
    }
    m_pChartView = new CChartView();


    m_pVboxLay->addWidget(m_pMybar1);
    m_pVboxLay->addWidget(m_pDateTable);
    m_pVboxLay->addWidget(m_pChartView);
    m_pChartView->hide();
//    m_pVboxLay->setSizeConstraint(QLayout::SetDefaultConstraint);

    connect(m_pChoice,SIGNAL(clicked(bool)),this,SLOT(slot_ChoiceFile()));
    connect(m_pAddDate,SIGNAL(clicked(bool)),this,SLOT(slot_Adddata()));
//    connect(m_pPointcloud,SIGNAL(clicked(bool)),this,SLOT(slot_createPointcloud()));
    connect(m_pSearchLine,SIGNAL(editingFinished()),this,SLOT(slot_searchByText()));
    connect(m_pDaochuDate,SIGNAL(clicked(bool)),this,SLOT(slot_moveOutdata()));

}

void CTableView::slot_Adddata()
{
    if(!m_pFileName->text().isEmpty())
        directory=m_pFileName->text();

    if(m_pDateTable->rowCount()>0)
        m_pDateTable->clearContents();

    if (!directory.isEmpty() && ((directory.endsWith(".xlsx")) ||(directory.endsWith(".xls"))))
    {
        QFile f(directory);
        if(!f.exists())
        {
            QMessageBox::warning(this,"warning","文件输入不正确!",QMessageBox::Ok | QMessageBox::Cancel,QMessageBox::Cancel);

            qDebug()<<"文件输入不对";
            return;
        }
        else
        {
            excel_read(directory,m_pDateTable);
        }

    }
    else {
        QMessageBox::warning(this,"warning","文件输入不正确!",QMessageBox::Ok | QMessageBox::Cancel,QMessageBox::Cancel);

        qDebug()<<"文件输入不对";
        return;
    }

}

void CTableView::slot_ChoiceFile()
{
    if (!directory.isEmpty())
        directory.remove(directory.split("/").last());

//    directory=QDir::toNativeSeparators(QFileDialog::getOpenFileName(this, tr("Find Files"), directory));
    directory=QDir::toNativeSeparators(QFileDialog::getOpenFileName(this,QString(),QString(),"excel(*.xls *.xlsx)"));

    qDebug()<<directory ;

    if (!directory.isEmpty() && ((directory.endsWith(".xlsx")) ||(directory.endsWith(".xls"))))
    {
        m_pFileName->setText(directory);
    }
    else
        return;

    if(m_pDateTable->rowCount()>0)
        m_pDateTable->clearContents();

    ret.clear(); //清空数据
    m_pItem.clear(); //清空表头
    excel_read(directory,m_pDateTable);

    m_pChartView->show();
    m_pChartView->Conbox_addItem();

    m_pDateTable->setFixedHeight(this->height()/6);

}


void CTableView::excel_read(QString &path, QTableWidget *tablewidget)
{
    QElapsedTimer timer;
    timer.start();
    QAxObject excel("Excel.Application");
    //不对excel进行显示
    excel.setProperty("Visible",false);
    //添加新的工作簿
    QAxObject *workbooks = excel.querySubObject("WorkBooks");
    //打开已存在的工作簿
    workbooks->dynamicCall("Open (const QString&)",QString(path));
    //获取活动工作簿
    QAxObject *workbook = excel.querySubObject("ActiveWorkBook");
//    QAxObject *work_sheets = workbook->querySubObject("Sheets");  //Sheets也可换用WorkSheets
//    int sheet_count = work_sheets->property("Count").toInt();  //获取工作表数目

    //获取第一个工作表
    QAxObject *worksheet = workbook->querySubObject("Worksheets(int)", 1);

    qDebug()<<"open cost:"<<timer.elapsed()<<"ms";timer.restart();


    QAxObject *usedrange=worksheet->querySubObject("UsedRange");//获取整个worksheet的范围

    QVariant cell=usedrange->dynamicCall("value");//将worksheet的全部内容存储在QVariant变量中
//    QList<QList<QVariant>> ret;//将Qvariant转换为QList<QList<QVariant>>

    Qvariant2listlistVariant(cell,ret);
    qDebug()<<"read data cost:"<<timer.elapsed()<<"ms";timer.restart();

    TABLE_COL = ret.at(0).length();
    m_pDateTable->setColumnCount(TABLE_COL);
    QTableWidgetItem        *headerItem;
    QStringList             headerText;
    QHeaderView *header = m_pDateTable->verticalHeader();
    header->setHidden(true);

    for(int i =0; i <m_pDateTable->columnCount();i++ )
    {
        headerItem=new QTableWidgetItem(ret.at(0).at(i).toString());
        m_pItem<<ret.at(0).at(i).toString();
        QFont font = headerItem->font();
        font.setBold(true);
        font.setPointSize(12);
        m_pDateTable->setHorizontalHeaderItem(i,headerItem);

//        headerItem->setFlags(headerItem->flags() &~Qt::ItemIsEnabled & ~Qt::ItemIsSelectable);
    }

    TABLE_ROW = ret.length()-1;
    tablewidget->setRowCount(TABLE_ROW);

    ret.removeAt(0);

    for(int i=0;i<tablewidget->rowCount();i++)
    {
        if(ret.at(i).length()<=tablewidget->columnCount())
        {
            for(int j=0;j<ret.at(i).length();j++)
            {
                QString strVal=ret.at(i).at(j).toString();
                if(strVal.length() > 7)
                {
                    float num = strVal.toFloat();
                    strVal = QString::asprintf("%.3f", num);//格式化，自动四舍五入
                }

                //将单元格的内容放置在table表中
                tablewidget->setItem(i,j,new QTableWidgetItem(strVal));
                tablewidget->item(i,j)->setTextAlignment(Qt::AlignVCenter|Qt::AlignHCenter);
            }

        }

    }
    qDebug()<<"show data cost:"<<timer.elapsed()<<"ms";timer.restart();

    workbook->dynamicCall("Close()");      //关闭工作簿
    excel.dynamicCall("Quit()");           //关闭excel
}

//快速将数据写入excel中
void CTableView::excel_write(QString &path, QTableWidget *tablewidget)
{
    QAxObject excel("Excel.Application");
    //不对excel进行显示
    excel.setProperty("Visible",false);
    //获取工作薄集合
    QAxObject *workbooks = excel.querySubObject("WorkBooks");
    //新建一个工作簿
    workbooks->dynamicCall("Add");
    //获取当前工作簿
    QAxObject *workbook = excel.querySubObject("ActiveWorkBook");
    //获取第一个工作表
    QAxObject *worksheet = workbook->querySubObject("Worksheets(int)", 1);

    //将tablewidget中的内容添加到excel中
    char m=0X40+tablewidget->columnCount();//0X41表示'A'
    QString range=QString("A1:%1%2").arg(m).arg(tablewidget->rowCount()+1);//根据tablewidget获得excel的范围
    QAxObject *cell=worksheet->querySubObject("Range(const QString&)",range);//设置excel的范围

    QList<QList<QVariant>> all_data;//用于存储tablewidgt中的所有数据
    for(int k=0;k<=tablewidget->rowCount();k++)
    {
        QList<QVariant> row_data;
        for(int j=0;j<tablewidget->columnCount();j++)
        {
            if(k==0)
            {
                row_data.append(tablewidget->horizontalHeaderItem(j)->text());//表头
            }
            else {
                row_data.append(tablewidget->item(k-1,j)->text());//数据
            }
        }
        all_data.append(row_data);
    }
    QVariant res;
    castListListVariant2Variant(all_data, res);//将QList<QList<QVariant> > 转为QVariant
    cell->setProperty("Value",res);

    //将生成的excel文件保存到指定目录下
    workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(path));//转换地址的表示方式
    workbook->dynamicCall("Close()");      //关闭工作簿
    excel.dynamicCall("Quit()");           //关闭excel
}


void CTableView::Qvariant2listlistVariant(const QVariant &var, QList<QList<QVariant> > &ret)
{
    //将Qvariant转换为QList<QList<QVariant>>

    QVariantList varrows=var.toList();
    if(varrows.isEmpty())
    {
        return;
    }
    else {
        const int rowcount=varrows.size();//行数
        QVariantList rowdata;
        for(int i=0;i<rowcount;i++)
        {
            rowdata=varrows[i].toList();//将每一行的值存入到list中
            ret.push_back(rowdata);
        }
    }

}

//把QList<QList<QVariant> > 转为QVariant,用于快速写入的
void CTableView::castListListVariant2Variant(const QList<QList<QVariant> > &cells, QVariant &res)
{
    QVariantList vars;
    const int rows = cells.size();//获取行数
    for(int i=0;i<rows;++i)
    {
        vars.append(QVariant(cells[i]));//将list(i)添加到QVariantList中 QVariant(cells[i])强制转换
    }
    res = QVariant(vars);//强制转换
}


void CTableView::slot_createPointcloud()
{

    m_pChartView->show();
    m_pDateTable->setFixedHeight(this->height()/6);
}


void CTableView::ScreenInfo(QTableWidget *source, QString &text)
{
    int rC = source->rowCount();//获得行数
    if ("" == text) {//判断输入是否为空
        for (int i = 0; i < rC; i++) {
            source->setRowHidden(i, false);//显示所有行
        }
    }
    else {
        //获取符合条件的cell索引
        QList <QTableWidgetItem *> item = source->findItems(text, Qt::MatchContains);

        for (int i = 0; i < rC; i++) {
            source->setRowHidden(i, true);//隐藏所有行
        }

        if (!item.isEmpty()) { //不为空
            for (int i = 0; i < item.count(); i++) {
                source->setRowHidden(item.at(i)->row(), false);//item.at(i).row()输出行号
            }
        }
    }
}

void CTableView::slot_searchByText()
{
    QString text = m_pSearchLine->text();
    ScreenInfo(m_pDateTable, text);
}

void CTableView::slot_moveOutdata()
{
    QString path_ = "";
    path_=QDir::toNativeSeparators(QFileDialog::getSaveFileName(this,QString(),QDir::currentPath(),"excel(*.xlsx *.xls)"));

    if(!path_.isEmpty())
        excel_write(path_,m_pDateTable);

}
